﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;

namespace ReportsFactory
{
    public partial class DBConnections : System.Web.UI.Page
    {
        public Report CurrentReport
        {
            get
            {
                if (Session["CurrentReport"].IsNull())
                    Session["CurrentReport"] = new Report();

                return Session["CurrentReport"] as Report;
            }

            set
            {
                Session["CurrentReport"] = value;
            }
        }

        #region General DB

        private DB.DBTypes DBType
        {
            get
            {
                return DB.DBTypes.SQLSever;
            }
        }

        private bool IsWindowsAuthentication
        {
            get
            {
                return ddlAuthentication.SelectedItem.Value == "Windows Authentication";
            }
        }

        #endregion

        #region Step 1 - Welcome
        protected void cmdRunReport_Click(object sender, EventArgs e)
        {
            reportsDiv.Visible = true;
            startDiv.Visible = false;

            rdlstMyReports.Items.Clear();
            rdlstPublicReports.Items.Clear();
            var reports = Common.GetAllReportFileNames();
            var ser = new Serializer<Report>();
            foreach (string filename in reports)
            {
                Report rpt = new Report();
                rpt = ser.DeSerialize(Common.ReportsFolder + filename);
                if (rpt.Owner.ToLower() == Common.Username.ToLower())
                    rdlstMyReports.Items.Add(new ListItem() { Text = rpt.Name, Value = rpt.ID });
                else if (rpt.IsPublic)
                    rdlstPublicReports.Items.Add(new ListItem() { Text = rpt.Name, Value = rpt.ID });
            }
        }

        protected void cmdEditReport_Click(object sender, EventArgs e)
        {
            throw new NotImplementedException();
        }

        protected void cmdCreateReport_Click(object sender, EventArgs e)
        {
            CurrentReport = new Report();
            startDiv.Visible = false;
            dbDiv.Visible = true;
        }
        #endregion

        #region Step 2 - Add Connections
        protected void ddlAuthentication_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (IsWindowsAuthentication)
                txtUserName.Text = txtPassword.Text = "";

            txtUserName.Enabled = txtPassword.Enabled = !IsWindowsAuthentication;
        }

        private bool TestConnection()
        {
            try
            {
                if (DBType == DB.DBTypes.SQLSever)
                {
                    SqlConnection sqlCon = new SqlConnection(DB.GenerateCompleteConnectionString(DBType, txtServerName.Text, txtDBName.Text, txtUserName.Text, txtPassword.Text));
                    sqlCon.Open();
                }
                else
                {
                    OleDbConnection sqlCon = new OleDbConnection(DB.GenerateCompleteConnectionString(DBType, txtServerName.Text, txtDBName.Text, txtUserName.Text, txtPassword.Text));
                    sqlCon.Open();
                }
                spnMsg.InnerHtml = "<span style='color: Green'>Connection Succeeded</span>";
                return true;
            }
            catch (Exception ex)
            {
                spnMsg.InnerHtml = "<span style='color: Red'>Error in connection: <br /></span><i>" + ex.Message + "</i>";
            }

            return false;
        }

        protected void cmdTestCon_Click(object sender, EventArgs e)
        {
            TestConnection();
        }

        protected void cmdAddCon_Click(object sender, EventArgs e)
        {
            if (txtDBName.Text.IsNotEmpty() && txtServerName.Text.IsNotEmpty() && TestConnection())
            {
                bool addToList = true;
                foreach (ListItem item in chkLstConnections.Items)
                {
                    if (item.Text.ToLower() == DB.GenerateDBID(txtServerName.Text, txtDBName.Text).ToLower())
                        addToList = false;
                }

                if (addToList)
                {
                    ListItem item = new ListItem()
                                    {
                                        Value = DB.GenerateCompleteConnectionString(DBType, txtServerName.Text, txtDBName.Text, txtUserName.Text, txtPassword.Text),
                                        Text = DB.GenerateDBID(txtServerName.Text, txtDBName.Text),
                                        Selected = false
                                    };

                    chkLstConnections.Items.Add(item);
                    ddlConnectionStrings.Items.Add(item);

                    ddlConnectionStrings_SelectedIndexChanged(sender, e);
                }
            }
        }

        protected void cmdRemoveCon_Click(object sender, EventArgs e)
        {
            string valToRemove = "";
            for (int i = chkLstConnections.Items.Count - 1; i >= 0; i--)
            {
                if (chkLstConnections.Items[i].Selected)
                {
                    valToRemove = chkLstConnections.Items[i].Value;
                    chkLstConnections.Items.RemoveAt(i);
                }

            }

            for (int i = ddlConnectionStrings.Items.Count - 1; i >= 0; i--)
            {
                if (ddlConnectionStrings.Items[i].Value == valToRemove)
                    ddlConnectionStrings.Items.RemoveAt(i);
            }
        }

        protected void cmdDBPrevious_Click(object sender, EventArgs e)
        {
            startDiv.Visible = true;
            dbDiv.Visible = false;
        }

        protected void cmdDBNext_Click(object sender, EventArgs e)
        {
            dbDiv.Visible = false;
            tablesDiv.Visible = true;
        }

        protected void ddlConnectionStrings_SelectedIndexChanged(object sender, EventArgs e)
        {
            DataTable tables = new DataTable("Tables");
            using (SqlConnection connection = new SqlConnection(ddlConnectionStrings.SelectedItem.Value))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "select table_schema + '.' + table_name as [Name] from INFORMATION_SCHEMA.Tables order by table_schema, table_name";

                connection.Open();
                tables.Load(command.ExecuteReader(CommandBehavior.CloseConnection));

                chkLstTables.DataSource = tables;
                chkLstTables.DataTextField = "Name";
                chkLstTables.DataBind();
            }

        }
        #endregion

        #region Step 2A - Run Reports
        protected void cmdReportsNext_Click(object sender, EventArgs e)
        {
            string rptId = "";
            if (rdlstPublicReports.SelectedIndex > -1)
                rptId = rdlstPublicReports.SelectedItem.Value;
            else if (rdlstMyReports.SelectedIndex > -1)
                rptId = rdlstMyReports.SelectedItem.Value;

            var reports = Common.GetAllReportFileNames();
            var ser = new Serializer<Report>();
            foreach (string filename in reports)
            {
                Report rpt = new Report();
                rpt = ser.DeSerialize(Common.ReportsFolder + filename);
                if (rpt.ID == rptId)
                {
                    rpt.IsNew = false;
                    CurrentReport = rpt;
                    break;
                }
            }

            reportsDiv.Visible = false;
            saveRptDiv.Visible = true;
            SetReportVisibility();
        }

        protected void cmdReportsPrevious_Click(object sender, EventArgs e)
        {
            reportsDiv.Visible = false;
            startDiv.Visible = true;
        }
        #endregion

        #region Step 3 - Select Tables
        protected void cmdAddTables_Click(object sender, EventArgs e)
        {
            for (int i = chkLstTables.Items.Count - 1; i >= 0; i--)
            {
                if (chkLstTables.Items[i].Selected)
                {
                    ListItem item = new ListItem();
                    item.Text = chkLstTables.Items[i].Text;
                    item.Value = ddlConnectionStrings.SelectedItem.Value;

                    bool itemExist = false;
                    for (int x = chkLstSelectedTables.Items.Count - 1; x >= 0; x--)
                    {
                        if (chkLstSelectedTables.Items[x].Text.ToLower() == item.Text.ToLower() && chkLstSelectedTables.Items[x].Value.ToLower() == item.Value.ToLower())
                        {
                            itemExist = true;
                            break;
                        }
                    }

                    if (!itemExist)
                    {
                        chkLstSelectedTables.Items.Add(item);
                        CurrentReport.GetTable(ddlConnectionStrings.SelectedItem.Value, item.Text);
                    }
                }
            }
        }

        protected void cmdRemoveTables_Click(object sender, EventArgs e)
        {
            for (int i = chkLstSelectedTables.Items.Count - 1; i >= 0; i--)
            {
                if (chkLstSelectedTables.Items[i].Selected)
                {
                    chkLstSelectedTables.Items.RemoveAt(i);

                    CurrentReport.RemoveTable(chkLstSelectedTables.Items[i].Value, chkLstSelectedTables.Items[i].Text);
                }
            }
        }

        protected void cmdTablesPrevious_Click(object sender, EventArgs e)
        {
            dbDiv.Visible = true;
            tablesDiv.Visible = false;
        }

        protected void cmdTablesNext_Click(object sender, EventArgs e)
        {
            columnsDiv.Visible = true;
            tablesDiv.Visible = false;

        }
        #endregion

        #region Step 4 - Setup Relations & Select Cols
        protected void cmdColsNext_Click(object sender, EventArgs e)
        {
            columnsDiv.Visible = false;
            filterDiv.Visible = true;
            SetupFilters();
        }

        protected void cmdColsPrevious_Click(object sender, EventArgs e)
        {
            columnsDiv.Visible = false;
            tablesDiv.Visible = true;
        }

        private void SetupFilters()
        {
            List<Tuple<string, string, string>> cols = new List<Tuple<string, string, string>>();
            foreach (var table in CurrentReport.Tables)
                foreach (var col in table.Columns)
                    cols.Add(new Tuple<string, string, string>(table.TableId.ToString(), table.TableName, col.ColumnName));


            foreach (var item in cols)
            {
                HtmlTableRow row = new HtmlTableRow();

                HtmlTableCell cell = new HtmlTableCell();
                cell.InnerText = item.Item2;
                row.Cells.Add(cell);

                CheckBox cb = new CheckBox();
                cb.ID = item.Item1 + "||" + item.Item3;
                cb.CssClass = "filterColCb";
                cb.Text = item.Item3;
                cell = new HtmlTableCell();
                cell.Controls.Add(cb);
                row.Cells.Add(cell);

                DropDownList ddl = new DropDownList();
                ddl.CssClass = "filterColDdl";
                ddl.Items.Clear();
                Report.ValidOperands.ForEach(c => ddl.Items.Add(new ListItem() { Text = c, Value = c }));
                cell = new HtmlTableCell();
                cell.Controls.Add(ddl);
                row.Cells.Add(cell);

                TextBox tb = new TextBox();
                tb.CssClass = "filterColTb";
                cell = new HtmlTableCell();
                cell.Controls.Add(tb);
                row.Cells.Add(cell);

                Literal aliasLit = new Literal();
                aliasLit.Text = "Alias: ";
                TextBox aliasTb = new TextBox();
                aliasTb.CssClass = "aliasColTb";
                cell = new HtmlTableCell();
                cell.Controls.Add(aliasLit);
                cell.Controls.Add(aliasTb);
                row.Cells.Add(cell);

                filterTable.Rows.Add(row);
            }

        }
        #endregion

        #region Step 5  - Filter Cols
        protected void cmdFilterPrevious_Click(object sender, EventArgs e)
        {
            columnsDiv.Visible = true;
            filterDiv.Visible = false;
        }

        protected void cmdFilterNext_Click(object sender, EventArgs e)
        {
            filterDiv.Visible = false;
            saveRptDiv.Visible = true;
            SetReportVisibility();
        }
        #endregion

        #region Step 6 - Save Report
        protected void chkboxSaveReport_CheckedChanged(object sender, EventArgs e)
        {
            trMakePublic.Visible = trReportName.Visible = chkboxSaveReport.Checked;

            if (!chkboxSaveReport.Checked)
                chkboxMakePublic.Checked = false;

        }

        protected void cmdPreviewPrevious_Click(object sender, EventArgs e)
        {
            saveRptDiv.Visible = false;
            if (!CurrentReport.IsNew)
                reportsDiv.Visible = true;
            else
            {
                columnsDiv.Visible = true;
                GenerateTables();
            }
        }

        protected void cmdPreviewNext_Click(object sender, EventArgs e)
        {
            if (CurrentReport.IsNew)
            {
                CurrentReport.CreatedDate = DateTime.Now;
                CurrentReport.Owner = Common.Username;
            }

            if (CurrentReport.IsCurrentUserOwner)
            {
                CurrentReport.Name = txtReportName.Text;
                CurrentReport.IsPublic = chkboxMakePublic.Checked;
            }

            if (chkboxSaveReport.Checked && trSaveReport.Visible)
                CurrentReport.SaveReport();

            Response.Write("Report generated!");
        }

        protected void SetReportVisibility()
        {
            if (!CurrentReport.IsNew)
            {
                txtReportName.Text = CurrentReport.Name;
                chkboxMakePublic.Checked = CurrentReport.IsPublic;
                trSaveReport.Visible = CurrentReport.IsCurrentUserOwner;
                chkboxSaveReport.Checked = true;
            }
            else
            {
                chkboxSaveReport.Checked = false;
                chkboxSaveReport_CheckedChanged(null, null);
                txtReportName.Text = "";
            }

            chkboxMakePublic.Enabled = CurrentReport.IsCurrentUserOwner;
            txtReportName.Enabled = CurrentReport.IsCurrentUserOwner;
            chkboxSaveReport.Enabled = CurrentReport.IsCurrentUserOwner;
        }
        #endregion

        protected void Page_Load(object sender, EventArgs e)
        {
            if (Page.GetPostBackControl() == cmdTablesNext || Page.GetPostBackControl() == cmdFilterPrevious)
                GenerateTables();
            else if (Page.GetPostBackControl() == cmdColsNext)
                SaveTableState();
        }

        private void GenerateTables()
        {
            colsDiv.Controls.Clear();
            for (int i = chkLstSelectedTables.Items.Count - 1; i >= 0; i--)
            {
                Panel pnl = new Panel();
                CheckBoxList cbl = new CheckBoxList();
                pnl.Controls.Add(cbl);
                pnl.ScrollBars = ScrollBars.Auto;
                pnl.Height = new Unit(200);
                pnl.Attributes.CssStyle.Add("float", "left");
                pnl.CssClass = "draggable droppable ui-widget-content panel";
                cbl.CssClass = "draggableItem";
                colsDiv.Controls.Add(pnl);

                cbl.ToolTip = pnl.GroupingText = chkLstSelectedTables.Items[i].Text;
                pnl.ID = chkLstSelectedTables.Items[i].Text + "---" + chkLstSelectedTables.Items[i].Value;
                cbl.ID = chkLstSelectedTables.Items[i].Text + "----" + chkLstSelectedTables.Items[i].Value;

                using (SqlConnection sqlCon = new SqlConnection(chkLstSelectedTables.Items[i].Value))
                {
                    sqlCon.Open();

                    SqlCommand command = sqlCon.CreateCommand();
                    command.CommandText = "select column_name as COLNAME from information_schema.columns where table_name ='" + GetTableName(pnl.GroupingText) + "' order by column_name";

                    DataTable tables = new DataTable();
                    tables.Load(command.ExecuteReader(CommandBehavior.CloseConnection));

                    cbl.DataSource = tables;
                    cbl.DataTextField = "COLNAME";
                    cbl.DataBind();

                    if (command != null)
                        command.Dispose();
                }

                List<string> selectedData = new List<string>();
                if (hdnTableData.Value.IsNotNull())
                {
                    var splitd = hdnTableData.Value.Split(new string[] { "||" }, StringSplitOptions.RemoveEmptyEntries);
                    foreach (var s in splitd)
                    {
                        string fldName = s.Substring(s.LastIndexOf('^') + 1);
                        if (s.ToLower().StartsWith(cbl.ID.ToLower()) && cbl.Items.FindByText(fldName) != null)
                            cbl.Items.FindByText(fldName).Selected = true;
                    }
                }

                ClientScript.RegisterStartupScript(this.GetType(), "DrawingLines", "ReDrawLines();", true);
            }
        }

        private void SaveTableState()
        {
            lstRelations.Items.Clear();
            var rels = hdnRelationData.Value.Split(new string[] { "||" }, StringSplitOptions.RemoveEmptyEntries);
            rels.ToList().ForEach(c => lstRelations.Items.Add(c));

            if (hdnTableData.Value.IsNotEmpty())
            {
                string[] selectCols = hdnTableData.Value.Split(new string[] { "||" }, StringSplitOptions.RemoveEmptyEntries);

                foreach (string row in selectCols)
                {
                    string[] spltd1 = row.Split(new string[] { "----" }, StringSplitOptions.RemoveEmptyEntries);
                    string[] spltd2 = spltd1[1].Split('^');

                    string tableName = spltd1[0];
                    string connection = spltd2[0];
                    string column = spltd2[1];

                    Report.Table tableStruct = CurrentReport.GetTable(connection, tableName);
                    tableStruct.GetColumn(column);
                }
            }

            if (hdnRelationData.Value.IsNotEmpty())
            {
                string[] relations = hdnRelationData.Value.Split(new string[] { "||" }, StringSplitOptions.RemoveEmptyEntries);

                foreach (string row in relations)
                {
                    string[] spltd1 = row.Split('^');
                    string[] spltd2;

                    spltd2 = spltd1[0].Split(new string[] { "---" }, StringSplitOptions.RemoveEmptyEntries);
                    string srcTableName = spltd2[0];
                    string srcConnection = spltd2[1];
                    string srcColumn = spltd1[1];

                    spltd2 = spltd1[2].Split(new string[] { "---" }, StringSplitOptions.RemoveEmptyEntries);
                    string tgtTableName = spltd2[0];
                    string tgtConnection = spltd2[1];
                    string tgtColumn = spltd1[3];

                    Report.Table srcTab = CurrentReport.GetTable(srcConnection, srcTableName);
                    Report.Table tgtTab = CurrentReport.GetTable(tgtConnection, tgtTableName);

                    if (!CurrentReport.RelationExist(srcColumn, tgtColumn, tgtTab))
                        srcTab.Relations.Add(new Report.Relation()
                                            {
                                                TargetTable = tgtTab,
                                                SourceColumn = srcTab.GetColumn(srcColumn),
                                                TagetColumn = tgtTab.GetColumn(tgtColumn)
                                            });

                }
            }
        }

        private string GetTableName(string str)
        {
            if (!str.Contains("."))
                return str;
            else
                return str.Substring(str.LastIndexOf(".") + 1);
        }

    }
}